# -*- coding: utf-8 -*-
"""
Created on Tue Jun 11 15:29:42 2024

@author: wangwenjie
"""

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
from matplotlib.pyplot import MultipleLocator
from matplotlib.dates import MonthLocator
from collections import OrderedDict
from sqlalchemy import create_engine
from tqdm import tqdm
import datetime
import time
import math
import os

plt.rcParams['font.sans-serif'] = ['SimHei']
plt.rcParams['axes.unicode_minus'] = False
plt.style.use('ggplot')

#%% 画图
def fig_nav(fn, Port, title, ncol, path):
    fig = plt.figure(figsize=(30, 15))
    ax1 = fig.add_subplot(111)
    ax1.set_title('{}'.format(fn), fontsize=40)
    for c in Port.columns:
        ax1.plot(Port[c], linewidth=5, label=c)
    for ticks in plt.gca().xaxis.get_major_ticks():
        ticks.label1.set_fontsize(20) 
        ticks.label1.set_color('black')
    for ticks in plt.gca().yaxis.get_major_ticks():
        ticks.label1.set_fontsize(20)
        ticks.label1.set_color('black')
    x_major_locator = MultipleLocator(200)
    ax1.xaxis.set_major_locator(x_major_locator)
    plt.gca().set_facecolor('white')
    handles, labels = plt.gca().get_legend_handles_labels()
    by_label = OrderedDict(zip(labels, handles))
    plt.legend(by_label.values(), by_label.keys(), loc='center left', bbox_to_anchor=(1,0.5), ncol=ncol, fontsize=25)
    plt.savefig(os.path.join(path, '{}.png'.format(fn)), bbox_inches='tight')
    return

def fig_bar(Port, title, path):
    fig = plt.figure(figsize=(12,6))
    ax = fig.add_subplot(1,1,1)
    Port.plot.bar(ax=ax)
    for tick in ax.get_xticklabels():
        tick.set_rotation(0)
    ax.legend(loc = 'best')
    ax.set_title('{}'.format(title), fontsize = 25)
    plt.savefig(os.path.join(path, '{}.png'.format(title)), bbox_inches='tight')    
    return

#%% 计算IC
def output_ic(factor_names, df_ic):
    # 表格-IC相关统计
    result_ic = pd.DataFrame(index=factor_names, columns=['IC均值','|IC|>2%占比'])
    ic_added = df_ic.copy()
    ic_added = ic_added.rename(columns = {ic_added.columns[0]:'t_date'})
    ic_added = ic_added.set_index('t_date')
    ic_danqi = ic_added - ic_added.shift(1)
    ic_danqi.iloc[0,:] = ic_added.iloc[0,:]
    for fn in factor_names:
        result_ic.loc[fn,'IC均值'] = ic_danqi[fn].mean()
        result_ic.loc[fn,'|IC|>2%占比'] = ic_danqi[abs(ic_danqi)>0.02][fn].count() / ic_danqi[fn].count()
    # 画图-IC单期
    fig_path = os.path.join(new_dir, 'IC-单期')
    if not os.path.exists(fig_path):
        os.makedirs(fig_path)
    for fn in factor_names:
        fig_nav(fn, pd.DataFrame(ic_danqi[fn]), '{}_单期IC'.format(fn), 1, fig_path)
    # 画图-IC累计
    fig_path = os.path.join(new_dir, 'IC-累计')
    if not os.path.exists(fig_path):
        os.makedirs(fig_path)
    for fn in factor_names:
        fig_nav(fn, pd.DataFrame(ic_added[fn]), '{}_累计IC'.format(fn), 1, fig_path)
    return result_ic
    

#%% 计算超额
def output_chaoe(factor_names):
    chaoe_output = pd.DataFrame(index=factor_names, columns=['区间起点','区间终点','第1组年化超额','第5组年化超额'])
    for fn in factor_names:
        df = pd.read_excel(os.path.join(new_dir, '分组超额净值/{}_分组超额.xlsx').format(fn))
        df = df.rename(columns = {df.columns[0]:'t_date'})
        df = df.set_index('t_date')
        df = df.dropna(axis=0)
        chaoe_output.loc[fn,'区间起点'] = df.index[0].strftime('%Y-%m-%d')
        chaoe_output.loc[fn,'区间终点'] = df.index[-1].strftime('%Y-%m-%d')
        chaoe_output.loc[fn,'第1组年化超额'] = df[1][-1]**(365/(df.index[-1]-df.index[0]).days)-1
        chaoe_output.loc[fn,'第5组年化超额'] = df[5][-1]**(365/(df.index[-1]-df.index[0]).days)-1
    return chaoe_output

#%% 单调性
def output_monotonic(factor_names):
    for fn in factor_names:
        df = pd.read_excel(os.path.join(new_dir, '分组超额净值/{}_分组超额.xlsx').format(fn))
        df = df.rename(columns = {df.columns[0]:'t_date'})
        df = df.set_index('t_date')
        df = df.dropna(axis=0)
        df['year'] = [datetime.date.strftime(x,'%Y') for x in pd.to_datetime(df.index)]
        def cal_ret(df_slice):
            df_ret = df_slice.iloc[-1,:] / df_slice.iloc[0,:] - 1
            return df_ret
        df_ret = df.groupby('year')[df.columns[:-1]].apply(cal_ret)
        fig_path = os.path.join(new_dir, '超额分年单调性')
        if not os.path.exists(fig_path):
            os.makedirs(fig_path)
        fig_bar(df_ret, '{}'.format(fn), fig_path)
    return

#%% 月胜率
def output_winrate(factor_names):
    winrate_output = pd.DataFrame(index=factor_names, columns=['第1组','第2组','第3组','第4组','第5组'])
    for fn in factor_names:
        df = pd.read_excel(os.path.join(new_dir, '分组超额净值/{}_分组超额.xlsx').format(fn))
        df = df.rename(columns = {df.columns[0]:'t_date'})
        df = df.set_index('t_date')
        df = df.dropna(axis=0)
        df['month'] = [datetime.date.strftime(x,'%Y-%m') for x in pd.to_datetime(df.index)]
        def cal_ret(df_slice):
            df_ret = df_slice.iloc[-1,:] / df_slice.iloc[0,:] - 1
            return df_ret
        df_ret = df.groupby('month')[df.columns[:-1]].apply(cal_ret)
        df_winrate = df_ret.copy()
        df_winrate[df_winrate > 0] = 1
        df_winrate[df_winrate <= 0] = 0
        winrate_output.loc[fn] = list(df_winrate.sum(0) / df_winrate.count(0))
    return winrate_output

#%% 换手率
def output_turnover(factor_names):
    turnover_output = pd.DataFrame(index=factor_names, columns=['第1组','第2组','第3组','第4组','第5组'])
    for fn in factor_names:
        df = pd.read_excel(os.path.join(new_dir, '日换手率/{}.xlsx'.format(fn)))
        df = df.rename(columns = {df.columns[0]:'t_date'})
        df = df.set_index('t_date')
        df = df.dropna(axis=0)
        turnover_output.loc[fn] = list(df.sum(0) / df.count(0) * 252)
    return turnover_output

#%% excute
if __name__ == '__main__':
    start = '2022-06-30'
    end = '2024-06-28'
    method = 'close'
    freq = 1
    path = os.getcwd()
    new_dir = os.path.join(path, 'output/回测结果({}~{}_{})'.format(start, end, method))
    if not os.path.exists(new_dir):
        os.makedirs(new_dir)
    
    # 提取数据
    factor_names = [
                    'sec_convprice',
                    'sec_convsize',
                    'sec_conv_stocksize',
                    'sec_ytm',
                    'sec_bond_premium',
                    'sec_IV_BS',
                    'sec_IV_delta',
                    'sec_weightskew_63D',
                    'sec_convturnover',
                    'sec_volatility5d',
                    'sec_conv_amplitude',
                    'sec_volstability_10d',
                    'sec_ret',
                    'sec_priceratio_5d',
                    'sec_convPremium',
                    'sec_modified_premium',
                    'sec_double_low',
                    'sec_amplitude_delta',
                    'sec_ret_delta',
                    'sec_stock_mv',
                    'sec_stock_ret_20D',
                    'sec_EP_net_ttm',
                    'sec_EP_deducted_ttm',
                    'sec_SUE',
                    'sec_SUE_after',
                    
                    'ts_convprice_roll20d',
                    'ts_convturnover_roll20d',
                    'ts_volatility5d_roll20d',
                    'ts_amplitude_roll20d',
                    'ts_conv_premium_roll20d',
                    'ts_modified_premium_roll20d',
                    'ts_double_low_roll20d',
                    'ts_ret_delta_rol20d',
                    'ts_bond_premium_roll20d',
                    'ts_ret_roll20d',
                    'ts_IV_delta_roll20d',
                    'ts_IV_BS_roll20d',
                    'ts_ytm_roll20d',
                    'ts_volumerank_roll10d'
                    ]

    df_ic = pd.read_excel(os.path.join(new_dir, 'rank_ic.xlsx'), sheet_name='日均累计')
    result_ic = output_ic(factor_names, df_ic)
    result_ce = output_chaoe(factor_names)    # 超额
    result_wr = output_winrate(factor_names)  # 胜率
    result_tr = output_turnover(factor_names) # 换手
    output_monotonic(factor_names) # 超额单调性
    
    result_output = result_ic.copy()
    result_output['换仓频率'] = '{}-day'.format(freq)
    result_output['换仓时点'] = method
    result_output[result_ce.columns] = result_ce
    result_output['超额较高组年化换手'] = np.nan
    result_output['超额较高组月胜率'] = np.nan
    for fn in factor_names:
        if result_output.loc[fn,'第1组年化超额'] >= result_output.loc[fn,'第5组年化超额']:
            result_output.loc[fn, '超额较高组年化换手'] = result_tr.loc[fn, '第1组']
            result_output.loc[fn, '超额较高组月胜率'] = result_wr.loc[fn, '第1组']
        else:
            result_output.loc[fn, '超额较高组年化换手'] = result_tr.loc[fn, '第5组']
            result_output.loc[fn, '超额较高组月胜率'] = result_wr.loc[fn, '第5组']
            
    wb = pd.ExcelWriter(os.path.join(new_dir, "因子回测结果汇总.xlsx"))
    result_output.to_excel(wb)
    wb.save() 
            
    
    
    
    
    